import 'dart:io';
import 'package:jiajia_diy/diyProject/diy.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import 'dart:async';

/*
数据库类
 */
class DatabaseHelper {
//  static final DatabaseHelper _instance = DatabaseHelper.internal();
//  factory DatabaseHelper() => _instance;
  //定义数据库表名和字段名
  final String tableName = "diyTB1";
  final String columnDiyId = "id";
  final String columnDiyName = "name";
  final String columnDiyPlace = "place";
  final String columnDiyDate = "date";
  final String columnDiyPrice = "price";
  final String columnDiyItemCost = "itemCost";
  final String columnDiyLaborCost = "laborCost";
  final String columnDiyProfit = "profit";
  final String columnDiyLeader = "leader";
  final String columnDiyImage = "image";
  final String columnDiyNums = "nums";
  final String columnDiySinglePrice = "singlePrice";

  Database _db;

  //获取数据库
  Future<Database> get db async {
    if (_db != null) {
      print('数据库已存在');
      return _db;
    } else
      _db = await initDb();
    return _db;
  }

//  DatabaseHelper.internal();

  //进行数据库文件路径和版本配置新建
  initDb() async {
    Directory directory = await getApplicationDocumentsDirectory();
    String path = join(directory.path, "diy_db.db");
    var myDb = await openDatabase(path, version: 1, onCreate: _onCreate);
    return myDb;
  }

  //数据库表建立
  FutureOr _onCreate(Database db, int version) async {
    await db.execute('''create table $tableName(
    $columnDiyId integer primary key autoincrement,
    $columnDiyName text,
    $columnDiyPlace text,
    $columnDiyDate text,
    $columnDiyPrice int,
    $columnDiySinglePrice int,
    $columnDiyNums int,
    $columnDiyItemCost int,
    $columnDiyLaborCost int,
    $columnDiyProfit int,
    $columnDiyLeader text,
    $columnDiyImage text)''');
    print('Table is created');
  }

  //插入
  Future<int> saveDiy(Diy diy) async {
    Database database = await db;
    var result = await database.insert('$tableName', diy.toMap());
    print('我是保存数据的方法 本次保存的res: $result');
    return result;
  }

  //更新diy项目
  Future<int> updateDiy(Diy diy) async {
    Database database = await db;
    var result = await database.update("$tableName", diy.toMap(),
        where: "$columnDiyId = ?", whereArgs: [diy.id]);
    print('我是更新数据的方法 本次更新的res: $result');
    return result;
  }

  //获取diyItems
  Future<List> getDiys() async {
    Database database = await db;
    var result = await database
        .rawQuery("select * from $tableName order by $columnDiyId desc");
    print('我是获取diys的方法 result: $result');
    return result.toList();
  }

  //获取diy总数
  Future<int> getCount() async {
    Database database = await db;
    return Sqflite.firstIntValue(
        await database.rawQuery("select count(*) from $tableName"));
  }

  //获取单个diy
  Future<Diy> getDiy(int id) async {
    Database database = await db;
    var result = await database.rawQuery("select * from $tableName where $columnDiyId = $id");
    if (result.length == 0) {
      return null;
    } else {
      print('我是获取diy的方法 本次获得是diy数据是: ${result.first}');
      return Diy.fromMap(result.first);
    }
  }

  //删除diy项目
  Future<int> deleteDiy(int id) async {
    Database database = await db;
    var result = await database
        .delete("$tableName", where: "$columnDiyId = ?", whereArgs: [id]);
    return result;
  }

  //查询总金额
  Future<int> priceTotal() async{
    Database database = await db;
    var result = await database.rawQuery("select sum($columnDiyPrice) from $tableName");
    return result.first['sum(price)'];
  }

  //查询总利润
  Future<int> profitTotal() async{
    Database database = await db;
    var result = await database.rawQuery("select sum($columnDiyProfit) from $tableName");
    return result.first['sum(profit)'];
  }

  //关闭数据库
  Future close() async {
    Database database = await db;
    database.close();
  }
}
